package com.tsfyun.scm.util;

import cn.hutool.core.util.StrUtil;
import com.tsfyun.common.base.enums.BillTypeEnum;
import com.tsfyun.common.base.enums.TransactionModeEnum;
import com.tsfyun.common.base.enums.singlewindow.DyckTransModeEnum;
import com.tsfyun.common.base.util.LocalDateTimeUtils;
import com.tsfyun.common.base.util.StringUtils;
import com.tsfyun.scm.entity.customs.Declaration;
import com.tsfyun.scm.entity.system.Subject;
import com.tsfyun.scm.vo.customs.DeclarationMemberVO;
import jxl.write.WritableSheet;
import org.apache.commons.lang.ArrayUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.Optional;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class ExportUtil {

    /**
     * 自动扩展列宽
     */
    public static void changeSheetWidth(WritableSheet sheet) throws Exception {
        if (sheet == null || (sheet != null && sheet.getRows() == 0)) {// 判断sheet是否为空
            System.out.println("Sheet为空!");
        } else {
            // 通用的获取cell值的方式,getCell(int column, int row) 行和列
            int Rows = sheet.getRows();// 总行
            int Cols = sheet.getColumns();// 总列
            int columnBestWidth[] = new int[Cols];    //保存最佳列宽数据的数组
            String[][] str = new String[Rows][Cols];
            for (int j = 0; j < Cols; j++) {
                for (int i = 0; i < Rows; i++) {
                    str[i][j] = (sheet.getCell(j, i)).getContents();// getCell(Col,Row)获得单元格的值
                    int width = 0;
                    if(str[i][j].contains("\r\n")){
                        width = str[i][j].substring(0,str[i][j].indexOf("\r\n")).length()
                                + getChineseNum(str[i][j].substring(0,str[i][j].indexOf("\r\n"))) + 2;    ///汉字占2个单位长度
                    }else{
                        width = str[i][j].length() + getChineseNum(str[i][j]) + 2;    ///汉字占2个单位长度
                    }
                    if(width > 60){ //列最宽为60
                        width = 60;
                    }
                    if (columnBestWidth[j] < width) //求取到目前为止的最佳列宽
                        columnBestWidth[j] = width;
                }
            }
            for (int i = 0; i < columnBestWidth.length; i++) {//设置每列宽
                sheet.setColumnView(i, columnBestWidth[i]);
            }
        }
    }
    //统计context中是汉字的个数
    public static int getChineseNum(String context) {
        int lenOfChinese = 0;
        Pattern p = Pattern.compile("[\u4e00-\u9fa5]");//汉字的Unicode编码范围
        Matcher m = p.matcher(context);
        while (m.find()) {
            lenOfChinese++;
        }
        return lenOfChinese;
    }

    /**
     * 获取表头标题（有序列）
     * @param titles       标题
     * @return
     */
    public static List<List<String>> headListWithIndex(String... titles) {
        List<List<String>> headList = new ArrayList<List<String>>();

        List<String> head0 = new ArrayList<String>();
        head0.add("序号");
        headList.add(head0);

        if(ArrayUtils.isNotEmpty(titles)) {
            List<String> head;
            for(String title : titles) {
                head = new ArrayList<>();
                head.add(title);
                headList.add(head);
            }
        }
        return headList;
    }

    /**
     * 获取表头标题（无序列）
     * @param titles       标题
     * @return
     */
    public static List<List<String>> headListNoIndex(String... titles) {
        List<List<String>> headList = new ArrayList<List<String>>();

        if(ArrayUtils.isNotEmpty(titles)) {
            List<String> head;
            for(String title : titles) {
                head = new ArrayList<>();
                head.add(title);
                headList.add(head);
            }
        }
        return headList;
    }

    /**
     * 导出报关单草单
     * @param declaration
     * @param members
     * @param subject
     * @param subjectOverseasName
     * @return
     */
    public static HSSFWorkbook exportDeclareDraft(Declaration declaration, List<DeclarationMemberVO> members,
                                                  Subject subject, String subjectOverseasName,List<String> notShowFields) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFSheet sheet = workbook.createSheet("报关单");

        //黑色20号宋体加粗居中
        HSSFCellStyle black20BoldStyle = workbook.createCellStyle();
        black20BoldStyle.setAlignment(HorizontalAlignment.CENTER);
        black20BoldStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        black20BoldStyle.setWrapText(true);
        black20BoldStyle.setBorderBottom(BorderStyle.THIN);
        black20BoldStyle.setBorderTop(BorderStyle.THIN);
        black20BoldStyle.setBorderLeft(BorderStyle.THIN);
        black20BoldStyle.setBorderRight(BorderStyle.THIN);

        //黑色10号宋体居左
        HSSFCellStyle black10Style = workbook.createCellStyle();
        black10Style.setAlignment(HorizontalAlignment.LEFT);
        black10Style.setVerticalAlignment(VerticalAlignment.CENTER);
        black10Style.setWrapText(true);
        black10Style.setBorderBottom(BorderStyle.THIN);
        black10Style.setBorderTop(BorderStyle.THIN);
        black10Style.setBorderLeft(BorderStyle.THIN);
        black10Style.setBorderRight(BorderStyle.THIN);
        HSSFDataFormat format = workbook.createDataFormat();
        black10Style.setDataFormat(format.getFormat("@"));

        //红色10号宋体居左
        HSSFCellStyle red10Style = workbook.createCellStyle();
        red10Style.setAlignment(HorizontalAlignment.LEFT);
        red10Style.setVerticalAlignment(VerticalAlignment.CENTER);
        red10Style.setWrapText(true);
        red10Style.setBorderBottom(BorderStyle.THIN);
        red10Style.setBorderTop(BorderStyle.THIN);
        red10Style.setBorderLeft(BorderStyle.THIN);
        red10Style.setBorderRight(BorderStyle.THIN);

        //黑色12号宋体加粗居左
        HSSFCellStyle black12BoldStyle = workbook.createCellStyle();
        black12BoldStyle.setAlignment(HorizontalAlignment.LEFT);
        black12BoldStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        black12BoldStyle.setWrapText(true);
        black12BoldStyle.setBorderBottom(BorderStyle.THIN);
        black12BoldStyle.setBorderTop(BorderStyle.THIN);
        black12BoldStyle.setBorderLeft(BorderStyle.THIN);
        black12BoldStyle.setBorderRight(BorderStyle.THIN);

        //字体-宋体20号黑色加粗
        Font black20BoldFont = workbook.createFont();
        black20BoldFont.setFontName("宋体");
        black20BoldFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        black20BoldFont.setFontHeightInPoints((short) 20);
        black20BoldFont.setBold(true);
        black20BoldStyle.setFont(black20BoldFont);

        //字体-宋体10号黑色
        Font black10BlackFont = workbook.createFont();
        black10BlackFont.setFontName("宋体");
        black10BlackFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        black10BlackFont.setFontHeightInPoints((short) 10);
        black10BlackFont.setBold(false);
        black10Style.setFont(black10BlackFont);

        //字体-宋体10号红色
        Font red10Font = workbook.createFont();
        red10Font.setFontName("宋体");
        red10Font.setColor(HSSFColor.HSSFColorPredefined.RED.getIndex());
        red10Font.setFontHeightInPoints((short) 10);
        red10Font.setBold(false);
        red10Style.setFont(red10Font);

        //字体-宋体12号黑色加粗
        Font black12BoldFont = workbook.createFont();
        black12BoldFont.setFontName("宋体");
        black12BoldFont.setColor(HSSFColor.HSSFColorPredefined.BLACK.getIndex());
        black12BoldFont.setFontHeightInPoints((short) 12);
        black12BoldFont.setBold(true);
        black12BoldStyle.setFont(black12BoldFont);

        BillTypeEnum billTypeEnum = BillTypeEnum.of(declaration.getBillType());
        //大标题
        int rowNum = 0;
        HSSFRow row = sheet.createRow(rowNum);
        row.setHeightInPoints(20);
        row.setHeight((short) (40 * 20));
        Cell cell = row.createCell(0);
        cell.setCellValue(StrUtil.format("中华人民共和国海关{}货物报关单",billTypeEnum.getName()));
        cell.setCellStyle(black20BoldStyle);
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);
        cell = row.createCell(0);
        cell.setCellValue("预录入编号：");
        cell.setCellStyle(black10Style);

        //防止边框丢失
        cell = row.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);

        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        for(int i = 2;i <= 4;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 2, 4);
        sheet.addMergedRegion(region);

        cell = row.createCell(5);
        cell.setCellValue("申报口岸：");
        cell.setCellStyle(black10Style);

        cell = row.createCell(6);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);

        cell = row.createCell(7);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);

        region = new CellRangeAddress(rowNum, rowNum, 6, 7);
        sheet.addMergedRegion(region);

        cell = row.createCell(8);
        cell.setCellValue("海关编号：");
        cell.setCellStyle(black10Style);

        for (int i = 9;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 9, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //标题
        //境内收发货人
        cell = row.createCell(0);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "境内发货人" : "境内收货人");
        cell.setCellStyle(black10Style);
        cell = row.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        cell = row.createCell(2);
        cell.setCellValue(subject.getSocialNo());
        cell.setCellStyle(black10Style);
        cell = row.createCell(3);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 2, 3);
        sheet.addMergedRegion(region);

        //进出境关别
        cell = row.createCell(4);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "出境关别" : "进境关别");
        cell.setCellStyle(red10Style);

        cell = row.createCell(5);
        cell.setCellValue(new HSSFRichTextString(StrUtil.format("（{}）",!notShowFields.contains("iePortCode") ? declaration.getIePortCode() : "")));
        cell.setCellStyle(black10Style);
        cell.setCellType(CellType.STRING);

        //出口日期
        cell = row.createCell(6);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "出口日期" : "进口日期");
        cell.setCellStyle(black10Style);
        for(int i = 7;i <=9;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 7, 9);
        sheet.addMergedRegion(region);

        //申报日期
        cell = row.createCell(10);
        cell.setCellValue("申报日期");
        cell.setCellStyle(black10Style);

        for(int i = 11;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 11, 13);
        sheet.addMergedRegion(region);

        //备案号
        cell = row.createCell(14);
        cell.setCellValue("备案号");
        cell.setCellStyle(black10Style);

        cell = row.createCell(15);
        cell.setCellValue(StringUtils.null2EmptyWithTrim(declaration.getRecordNo()));
        cell.setCellStyle(black10Style);
        cell = row.createCell(16);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        cell = row.createCell(17);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        cell = row.createCell(18);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);

        region = new CellRangeAddress(rowNum, rowNum, 15, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //值
        //境内收发货人
        cell = row.createCell(0);
        cell.setCellValue(subject.getName());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 1;i <= 3;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black12BoldStyle);
        }
        region = new CellRangeAddress(rowNum, rowNum, 0, 3);
        sheet.addMergedRegion(region);

        //进出境关别
        cell = row.createCell(4);
        cell.setCellValue(!notShowFields.contains("iePortName") ? declaration.getIePortName() : "");
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(5);
        cell.setCellValue("");
        cell.setCellStyle(black12BoldStyle);
        region = new CellRangeAddress(rowNum, rowNum, 4, 5);
        sheet.addMergedRegion(region);

        //进出口日期
        cell = row.createCell(6);
        cell.setCellValue(Objects.nonNull(declaration.getImportDate()) ? LocalDateTimeUtils.formatTime(declaration.getImportDate(),"yyyy-MM-dd") : "");
        cell.setCellStyle(black12BoldStyle);
        for (int i = 7;i <= 9;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black12BoldStyle);
        }
        region = new CellRangeAddress(rowNum, rowNum, 6, 9);
        sheet.addMergedRegion(region);

        //申报日期
        for(int i = 10;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black12BoldStyle);
        }
        region = new CellRangeAddress(rowNum, rowNum, 10, 13);
        sheet.addMergedRegion(region);

        //备案号
        cell = row.createCell(14);
        cell.setCellValue(declaration.getRecordNo());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 15;i < 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black12BoldStyle);
        }
        region = new CellRangeAddress(rowNum, rowNum, 14, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //标题
        //境外收发货人
        cell = row.createCell(0);
        cell.setCellValue(StrUtil.format("{}", Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "境外收货人" : "境外发货人"));
        cell.setCellStyle(red10Style);
        cell = row.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        cell = row.createCell(2);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        cell = row.createCell(3);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 2, 3);
        sheet.addMergedRegion(region);

        //运输方式
        cell = row.createCell(4);
        cell.setCellValue("运输方式");
        cell.setCellStyle(black10Style);

        cell = row.createCell(5);
        cell.setCellValue(StrUtil.format("（{}）",declaration.getCusTrafModeCode()));
        cell.setCellStyle(black10Style);

        //运输工具名称及航次号
        cell = row.createCell(6);
        cell.setCellValue("运输工具名称及航次号");
        cell.setCellStyle(black10Style);
        for(int i = 7;i <= 9;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 6, 9);
        sheet.addMergedRegion(region);

        //提运单号
        cell = row.createCell(10);
        cell.setCellValue("提运单号");
        cell.setCellStyle(black10Style);

        for(int i = 11;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 11, 13);
        sheet.addMergedRegion(region);

        //进口货物存放地点，14-15合并单元格
        cell = row.createCell(14);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.IMP) ? "货物存放地点" : "");
        cell.setCellStyle(black10Style);
        cell = row.createCell(15);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 14, 15);
        sheet.addMergedRegion(region);

        for(int i = 16;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 16, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //境外收发货人
        cell = row.createCell(0);
        cell.setCellValue(subjectOverseasName);
        cell.setCellStyle(black12BoldStyle);
        for(int i = 1;i <= 3;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black12BoldStyle);
        }
        region = new CellRangeAddress(rowNum, rowNum, 0, 3);
        sheet.addMergedRegion(region);

        //运输方式
        cell = row.createCell(4);
        cell.setCellValue(declaration.getCusTrafModeName());
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(5);
        cell.setCellValue("");
        cell.setCellStyle(black12BoldStyle);
        region = new CellRangeAddress(rowNum, rowNum, 4, 5);
        sheet.addMergedRegion(region);

        //运输工具名称及航次号
        cell = row.createCell(6);
        cell.setCellValue(StrUtil.format("/{}",StringUtils.null2EmptyWithTrim(declaration.getCusVoyageNo())));
        for(int i = 7;i <= 9;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black12BoldStyle);
        }
        region = new CellRangeAddress(rowNum, rowNum, 6, 9);
        sheet.addMergedRegion(region);

        //提运单号
        cell = row.createCell(10);
        cell.setCellValue(declaration.getBillNo());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 11;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black12BoldStyle);
        }
        region = new CellRangeAddress(rowNum, rowNum, 10, 13);
        sheet.addMergedRegion(region);

        //14-15合并单元格，进口-货物存放地点
        cell = row.createCell(14);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.IMP) ? declaration.getGoodsPlace() : "");
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(15);
        cell.setCellValue("");
        cell.setCellStyle(black12BoldStyle);

        for(int i = 16;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black12BoldStyle);
        }
        region = new CellRangeAddress(rowNum, rowNum, 14, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //标题
        //生产销售单位/消费使用单位
        cell = row.createCell(0);
        cell.setCellValue(StrUtil.format("{}", Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "生产销售单位" : "消费使用单位"));
        cell.setCellStyle(black10Style);
        cell = row.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        cell = row.createCell(2);
        cell.setCellValue(declaration.getOwnerScc());
        cell.setCellStyle(black10Style);
        cell = row.createCell(3);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 2, 3);
        sheet.addMergedRegion(region);

        //监管方式
        cell = row.createCell(4);
        cell.setCellValue("监管方式");
        cell.setCellStyle(black10Style);

        cell = row.createCell(5);
        cell.setCellValue(StrUtil.format("（{}）",declaration.getSupvModeCdde()));
        cell.setCellStyle(black10Style);

        //征免性质
        cell = row.createCell(6);
        cell.setCellValue("征免性质");
        cell.setCellStyle(black10Style);

        cell = row.createCell(7);
        cell.setCellValue(declaration.getCutModeCode());
        cell.setCellStyle(black10Style);
        cell = row.createCell(8);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        cell = row.createCell(9);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 7, 9);
        sheet.addMergedRegion(region);

        //许可证号
        cell = row.createCell(10);
        cell.setCellValue("许可证号");
        cell.setCellStyle(black10Style);

        for(int i = 11;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 11, 13);
        sheet.addMergedRegion(region);

        //进口显示启运港，出口为空
        cell = row.createCell(14);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.IMP) ? "启运港" : "");
        cell.setCellStyle(black10Style);

        cell = row.createCell(15);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.IMP) ? StrUtil.format("（{}）",declaration.getDestPortCode()) : "");
        cell.setCellStyle(black10Style);
        for(int i = 16;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 15, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //值
        //生产销售单位/消费使用单位
        cell = row.createCell(0);
        cell.setCellValue(declaration.getOwnerName());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 1;i <= 3;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 0, 3);
        sheet.addMergedRegion(region);

        //监管方式
        cell = row.createCell(4);
        cell.setCellValue(declaration.getSupvModeCddeName());
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(5);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 4, 5);
        sheet.addMergedRegion(region);

        //征免性质
        cell = row.createCell(6);
        cell.setCellValue(declaration.getCutModeName());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 7;i <= 9;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 6, 9);
        sheet.addMergedRegion(region);

        //许可证号
        cell = row.createCell(10);
        cell.setCellValue(declaration.getLicenseNo());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 11;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 10, 13);
        sheet.addMergedRegion(region);

        //进口显示启运港，出口为空
        cell = row.createCell(14);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.IMP) ? declaration.getDestPortName() : "");
        cell.setCellStyle(black12BoldStyle);
        for(int i = 15;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 14, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //标题
       //合同协议号
        cell = row.createCell(0);
        cell.setCellValue("合同协议号");
        cell.setCellStyle(black10Style);
        cell = row.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        for(int i = 2;i <= 3;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 2, 3);
        sheet.addMergedRegion(region);

        //贸易国(地区)
        cell = row.createCell(4);
        cell.setCellValue("贸易国(地区)");
        cell.setCellStyle(black10Style);

        cell = row.createCell(5);
        cell.setCellValue(StrUtil.format("（{}）",declaration.getCusTradeNationCode()));
        cell.setCellStyle(black10Style);

        //启运国(地区)/运抵国(地区)
        cell = row.createCell(6);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "运抵国(地区)" : "启运国(地区)");
        cell.setCellStyle(black10Style);

        cell = row.createCell(7);
        cell.setCellValue(StrUtil.format("（{}）",declaration.getCusTradeCountryCode()));
        cell.setCellStyle(black10Style);
        for(int i = 8;i <= 9;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 7, 9);
        sheet.addMergedRegion(region);

        //经停港/指运港
        cell = row.createCell(10);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "指运港" : "经停港");
        cell.setCellStyle(black10Style);

        cell = row.createCell(11);
        cell.setCellValue(StrUtil.format("（{}）",declaration.getDistinatePortCode()));
        cell.setCellStyle(black10Style);
        for(int i = 12;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 11, 13);
        sheet.addMergedRegion(region);

        //入境口岸/离境口岸
        cell = row.createCell(14);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "离境口岸" : "入境口岸");
        cell.setCellStyle(red10Style);

        cell = row.createCell(15);
        cell.setCellValue(!notShowFields.contains("ciqEntyPortCode") ? StrUtil.format("（{}）",declaration.getCiqEntyPortCode()) : "()");
        cell.setCellStyle(black10Style);
        for(int i = 16;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 15, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //值
        //合同协议号
        cell = row.createCell(0);
        cell.setCellValue(declaration.getContrNo());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 1;i <= 3;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 0, 3);
        sheet.addMergedRegion(region);

        //贸易国(地区)
        cell = row.createCell(4);
        cell.setCellValue(declaration.getCusTradeNationName());
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(5);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 4, 5);
        sheet.addMergedRegion(region);

        //运抵国（地区）、启运国（地区）
        cell = row.createCell(6);
        cell.setCellValue(declaration.getCusTradeCountryName());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 7;i <= 9;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 6, 9);
        sheet.addMergedRegion(region);

        //指运港、经停港
        cell = row.createCell(10);
        cell.setCellValue(declaration.getDistinatePortName());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 11;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 10, 13);
        sheet.addMergedRegion(region);

        //入境口岸、离境口岸
        cell = row.createCell(14);
        cell.setCellValue(!notShowFields.contains("ciqEntyPortName") ? declaration.getCiqEntyPortName() : "");
        cell.setCellStyle(black12BoldStyle);
        for(int i = 15;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 14, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //标题
        //包装种类
        cell = row.createCell(0);
        cell.setCellValue("包装种类");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        cell = row.createCell(2);
        cell.setCellValue(StrUtil.format("（{}）",declaration.getWrapTypeCode()));
        cell.setCellStyle(black10Style);
        cell = row.createCell(3);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 2, 3);
        sheet.addMergedRegion(region);

        //件数
        cell = row.createCell(4);
        cell.setCellValue("件数");
        cell.setCellStyle(black10Style);

        //毛重（千克）
        cell = row.createCell(5);
        cell.setCellValue("毛重（千克）");
        cell.setCellStyle(black10Style);

        //净重（千克）
        cell = row.createCell(6);
        cell.setCellValue("净重（千克）");
        cell.setCellStyle(black10Style);
        cell = row.createCell(7);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 6, 7);
        sheet.addMergedRegion(region);

        //成交方式
        cell = row.createCell(8);
        cell.setCellValue("成交方式");
        cell.setCellStyle(black10Style);

        cell = row.createCell(9);
        cell.setCellValue(StrUtil.format("（{}）",Optional.ofNullable(DyckTransModeEnum.ofScode(declaration.getTransactionMode())).map(DyckTransModeEnum::getCode).orElse("")));
        cell.setCellStyle(black10Style);

        cell = row.createCell(10);
        cell.setCellValue("运费");
        cell.setCellStyle(black10Style);

        cell = row.createCell(11);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        cell = row.createCell(12);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 11, 12);
        sheet.addMergedRegion(region);

        cell = row.createCell(13);
        cell.setCellValue("保费");
        cell.setCellStyle(black10Style);

        cell = row.createCell(14);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        cell = row.createCell(15);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 14, 15);
        sheet.addMergedRegion(region);

        cell = row.createCell(16);
        cell.setCellValue("杂费");
        cell.setCellStyle(black10Style);

        cell = row.createCell(17);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        cell = row.createCell(18);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 17, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        //值
        //包装种类
        cell = row.createCell(0);
        cell.setCellValue(declaration.getWrapTypeName());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 1;i <= 3;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 0, 3);
        sheet.addMergedRegion(region);

        //件数
        cell = row.createCell(4);
        cell.setCellValue(declaration.getPackNo());
        cell.setCellStyle(black12BoldStyle);

        //毛重（千克）
        cell = row.createCell(5);
        cell.setCellValue(StringUtils.null2EmptyWithTrim(declaration.getGrossWt()));
        cell.setCellStyle(black12BoldStyle);

        //净重（千克）
        cell = row.createCell(6);
        cell.setCellValue(StringUtils.null2EmptyWithTrim(declaration.getNetWt()));
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(7);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 6, 7);
        sheet.addMergedRegion(region);

        //成交方式
        TransactionModeEnum transactionModeEnum = TransactionModeEnum.of(declaration.getTransactionMode());
        cell = row.createCell(8);
        cell.setCellValue(Optional.ofNullable(transactionModeEnum).map(TransactionModeEnum::getName).orElse(""));
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(9);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 8, 9);
        sheet.addMergedRegion(region);

        //运费
        cell = row.createCell(10);
        cell.setCellValue(declaration.getTransCosts());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 11;i <= 12;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 10, 12);
        sheet.addMergedRegion(region);

        //保费
        cell = row.createCell(13);
        cell.setCellValue(declaration.getInsuranceCosts());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 14;i <= 15;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 13, 15);
        sheet.addMergedRegion(region);

        //杂费
        cell = row.createCell(16);
        cell.setCellValue(declaration.getMiscCosts());
        cell.setCellStyle(black12BoldStyle);
        for(int i = 17;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 16, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        cell = row.createCell(0);
        cell.setCellValue("随附单证及编号");
        cell.setCellStyle(black10Style);
        cell = row.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        //暂赋值为空
        cell = row.createCell(2);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        for(int i = 3;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 2, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        cell = row.createCell(0);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 2);
        sheet.addMergedRegion(region);

        cell = row.createCell(3);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        for(int i = 4;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 3, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        cell = row.createCell(0);
        cell.setCellValue("标记唛码及备注：");
        cell.setCellStyle(black10Style);
        cell = row.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        cell = row.createCell(2);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        for(int i = 3;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 2, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        cell = row.createCell(0);
        cell.setCellValue("备注：");
        cell.setCellStyle(black10Style);

        for(int i = 1;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 1, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        for(int i = 0;i <= 10;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 0, 10);
        sheet.addMergedRegion(region);

        cell = row.createCell(11);
        cell.setCellValue("报关联系电话：");
        cell.setCellStyle(black10Style);
        for(int i = 12;i <= 13;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 11, 13);
        sheet.addMergedRegion(region);

        for(int i = 14;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 14, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        cell = row.createCell(0);
        cell.setCellValue("特殊关系确认:");
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(1);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 0, 1);
        sheet.addMergedRegion(region);

        cell = row.createCell(2);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);

        cell = row.createCell(3);
        cell.setCellValue("价格影响确认：");
        cell.setCellStyle(black12BoldStyle);

        cell = row.createCell(4);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);

        cell = row.createCell(5);
        cell.setCellValue("支付特许权使用费：");
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(6);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 5, 6);
        sheet.addMergedRegion(region);

        cell = row.createCell(7);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);

        cell = row.createCell(8);
        cell.setCellValue("自报自缴：");
        cell.setCellStyle(black12BoldStyle);
        cell = row.createCell(9);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 8, 9);
        sheet.addMergedRegion(region);

        cell = row.createCell(10);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);

        cell = row.createCell(11);
        cell.setCellValue("原产国/目的国/货源地未注明的项默认与提供的项相同");
        cell.setCellStyle(black10Style);
        for(int i = 12;i <= 18;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 11, 18);
        sheet.addMergedRegion(region);

        ++rowNum;
        row = sheet.createRow(rowNum);

        cell = row.createCell(0);
        cell.setCellValue("项号");
        cell.setCellStyle(black10Style);

        cell = row.createCell(1);
        cell.setCellValue("商品编号");
        cell.setCellStyle(red10Style);
        cell = row.createCell(2);
        cell.setCellValue("");
        cell.setCellStyle(red10Style);
        region = new CellRangeAddress(rowNum, rowNum, 1, 2);
        sheet.addMergedRegion(region);

        cell = row.createCell(3);
        cell.setCellValue("商品名称及规格型号");
        cell.setCellStyle(black10Style);
        for(int i = 4;i <= 5;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 3, 5);
        sheet.addMergedRegion(region);

        cell = row.createCell(6);
        cell.setCellValue("数量及单位");
        cell.setCellStyle(black10Style);
        cell = row.createCell(7);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 6, 7);
        sheet.addMergedRegion(region);

        cell = row.createCell(8);
        cell.setCellValue("单价/总价/币制");
        cell.setCellStyle(red10Style);
        cell = row.createCell(9);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 8, 9);
        sheet.addMergedRegion(region);

        cell = row.createCell(10);
        cell.setCellValue("原产国（地区）");
        cell.setCellStyle(black10Style);
        cell = row.createCell(11);
        cell.setCellValue("");
        cell.setCellStyle(black10Style);
        region = new CellRangeAddress(rowNum, rowNum, 10, 11);
        sheet.addMergedRegion(region);

        cell = row.createCell(12);
        cell.setCellValue("最终目的国（地区）");
        cell.setCellStyle(black10Style);
        for(int i = 13;i <= 14;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 12, 14);
        sheet.addMergedRegion(region);

        cell = row.createCell(15);
        cell.setCellValue(Objects.equals(billTypeEnum,BillTypeEnum.EXP) ? "境内货源地" : "境内目的地");
        cell.setCellStyle(red10Style);
        for(int i = 16;i <= 17;i++) {
            cell = row.createCell(i);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }
        region = new CellRangeAddress(rowNum, rowNum, 15, 17);
        sheet.addMergedRegion(region);

        cell = row.createCell(18);
        cell.setCellValue("征免");
        cell.setCellStyle(black10Style);

        for(int i = 0;i < members.size();i++) {

            ++rowNum;
            row = sheet.createRow(rowNum);

            DeclarationMemberVO member = members.get(i);
            cell = row.createCell(0);
            cell.setCellValue(i + 1);
            cell.setCellStyle(black10Style);

            cell = row.createCell(1);
            cell.setCellValue(member.getHsCode());
            cell.setCellStyle(black10Style);
            cell = row.createCell(2);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 1, 2);
            sheet.addMergedRegion(region);

            cell = row.createCell(3);
            cell.setCellValue(member.getName());
            cell.setCellStyle(black10Style);
            for(int k = 4;k <= 5;k++) {
                cell = row.createCell(k);
                cell.setCellValue("");
                cell.setCellStyle(black10Style);
            }
            region = new CellRangeAddress(rowNum, rowNum, 3, 5);
            sheet.addMergedRegion(region);

            //法一数量
            cell = row.createCell(6);
            cell.setCellValue(Objects.nonNull(member.getQuantity1()) ? StringUtils.null2EmptyWithTrim(member.getQuantity1()) : "");
            cell.setCellStyle(black10Style);

            //法一单位
            cell = row.createCell(7);
            cell.setCellValue(member.getUnit1Name());
            cell.setCellStyle(black10Style);

            //单价
            cell = row.createCell(8);
            cell.setCellValue(Objects.nonNull(member.getUnitPrice()) ? StringUtils.null2EmptyWithTrim(member.getUnitPrice()) : "");
            cell.setCellStyle(black10Style);
            cell = row.createCell(9);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 8, 9);
            sheet.addMergedRegion(region);

            //原产国（地区）
            cell = row.createCell(10);
            cell.setCellValue(member.getCountryName());
            cell.setCellStyle(black10Style);
            cell = row.createCell(11);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 10, 11);
            sheet.addMergedRegion(region);

            //最终目的国（地区）
            cell = row.createCell(12);
            cell.setCellValue(member.getDestinationCountryName());
            cell.setCellStyle(black10Style);
            for(int k = 13;k <= 14;k++) {
                cell = row.createCell(k);
                cell.setCellValue("");
                cell.setCellStyle(black10Style);
            }
            region = new CellRangeAddress(rowNum, rowNum, 12, 14);
            sheet.addMergedRegion(region);

            //境内货源地
            cell = row.createCell(15);
            cell.setCellValue(member.getDistrictName());
            cell.setCellStyle(black10Style);
            for(int k = 16;k <= 17;k++) {
                cell = row.createCell(k);
                cell.setCellValue("");
                cell.setCellStyle(black10Style);
            }
            region = new CellRangeAddress(rowNum, rowNum, 15, 17);
            sheet.addMergedRegion(region);

            //征免
            cell = row.createCell(18);
            cell.setCellValue(member.getDutyModeName());
            cell.setCellStyle(black10Style);

            ++rowNum;
            row = sheet.createRow(rowNum);

            cell = row.createCell(0);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);

            cell = row.createCell(1);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            cell = row.createCell(2);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 1, 2);
            sheet.addMergedRegion(region);

            cell = row.createCell(3);
            //cell.setCellValue(StringUtils.isNotEmpty(member.getBrand()) ? (StringUtils.null2EmptyWithTrim(member.getBrand()) + "牌") : "");
            cell.setCellValue("品牌：" + StringUtils.null2EmptyWithTrim(member.getBrand()) );
            cell.setCellStyle(black10Style);

            cell = row.createCell(4);
            //cell.setCellValue(StringUtils.isNotEmpty(member.getModel()) ? (StringUtils.null2EmptyWithTrim(member.getModel()) + "型") : "");
            cell.setCellValue("型号：" + StringUtils.null2EmptyWithTrim(member.getModel()) );
            cell.setCellStyle(black10Style);
            cell = row.createCell(5);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 4, 5);
            sheet.addMergedRegion(region);

            //法二数量
            cell = row.createCell(6);
            cell.setCellValue(Objects.nonNull(member.getQuantity2()) ? StringUtils.null2EmptyWithTrim(member.getQuantity2()) : "");
            cell.setCellStyle(black10Style);

            //法二单位
            cell = row.createCell(7);
            cell.setCellValue(member.getUnit2Name());
            cell.setCellStyle(black10Style);

            //总价
            cell = row.createCell(8);
            cell.setCellValue(Objects.nonNull(member.getTotalPrice()) ? StringUtils.null2EmptyWithTrim(member.getTotalPrice()) : "");
            cell.setCellStyle(black10Style);
            cell = row.createCell(9);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 8, 9);
            sheet.addMergedRegion(region);

            cell = row.createCell(10);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            cell = row.createCell(11);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 10, 11);
            sheet.addMergedRegion(region);

            cell = row.createCell(12);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            for(int k = 13;k <= 14;k++) {
                cell = row.createCell(k);
                cell.setCellValue("");
                cell.setCellStyle(black10Style);
            }
            region = new CellRangeAddress(rowNum, rowNum, 12, 14);
            sheet.addMergedRegion(region);

            cell = row.createCell(15);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            for(int k = 16;k <= 17;k++) {
                cell = row.createCell(k);
                cell.setCellValue("");
                cell.setCellStyle(black10Style);
            }
            region = new CellRangeAddress(rowNum, rowNum, 15, 17);
            sheet.addMergedRegion(region);

            cell = row.createCell(18);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);

            ++rowNum;
            row = sheet.createRow(rowNum);

            cell = row.createCell(0);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);

            cell = row.createCell(1);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            cell = row.createCell(2);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 1, 2);
            sheet.addMergedRegion(region);

            String declareSpec = StringUtils.removeSpecialSymbol(member.getDeclareSpec());
            if(declareSpec.endsWith("|")) {
                declareSpec = declareSpec.concat(" ");
            }

            cell = row.createCell(3);
            row.setHeightInPoints(20);
            row.setHeight((short) (60 * 20));
            cell.setCellValue(declareSpec);
            cell.setCellStyle(black10Style);
            for(int k = 4;k <= 5;k++) {
                cell = row.createCell(k);
                cell.setCellValue("");
                cell.setCellStyle(black10Style);
            }
            region = new CellRangeAddress(rowNum, rowNum, 3, 5);
            sheet.addMergedRegion(region);

            //成交数量
            cell = row.createCell(6);
            cell.setCellValue(Objects.nonNull(member.getQuantity()) ? StringUtils.null2EmptyWithTrim(member.getQuantity()) : "");
            cell.setCellStyle(black10Style);

            //成交单位
            cell = row.createCell(7);
            cell.setCellValue(member.getUnitName());
            cell.setCellStyle(black10Style);

            cell = row.createCell(8);
            cell.setCellValue(member.getCurrencyName());
            cell.setCellStyle(black10Style);
            cell = row.createCell(9);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 8, 9);
            sheet.addMergedRegion(region);

            cell = row.createCell(10);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            cell = row.createCell(11);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            region = new CellRangeAddress(rowNum, rowNum, 10, 11);
            sheet.addMergedRegion(region);

            cell = row.createCell(12);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            for(int k = 13;k <= 14;k++) {
                cell = row.createCell(k);
                cell.setCellValue("");
                cell.setCellStyle(black10Style);
            }
            region = new CellRangeAddress(rowNum, rowNum, 12, 14);
            sheet.addMergedRegion(region);

            cell = row.createCell(15);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
            for(int k = 16;k <= 17;k++) {
                cell = row.createCell(k);
                cell.setCellValue("");
                cell.setCellStyle(black10Style);
            }
            region = new CellRangeAddress(rowNum, rowNum, 15, 17);
            sheet.addMergedRegion(region);

            cell = row.createCell(18);
            cell.setCellValue("");
            cell.setCellStyle(black10Style);
        }

        //设置列宽
        sheet.setColumnWidth(0, 10 * 256);
        sheet.setColumnWidth(1, 10 * 256);
        sheet.setColumnWidth(2, 6 * 256);
        sheet.setColumnWidth(3, 25 * 256);
        sheet.setColumnWidth(4, 15 * 256);
        sheet.setColumnWidth(5, 15 * 256);
        sheet.setColumnWidth(6, 15 * 256);
        sheet.setColumnWidth(7, 8 * 256);
        sheet.setColumnWidth(8, 10 * 256);
        sheet.setColumnWidth(9, 8 * 256);
        sheet.setColumnWidth(10, 10 * 256);
        sheet.setColumnWidth(11, 6 * 256);
        sheet.setColumnWidth(12, 6 * 256);
        sheet.setColumnWidth(13, 6 * 256);
        sheet.setColumnWidth(14, 10 * 256);
        sheet.setColumnWidth(15, 6 * 256);
        sheet.setColumnWidth(16, 6 * 256);
        sheet.setColumnWidth(17, 6 * 256);
        sheet.setColumnWidth(18, 10 * 256);

        //默认行高
        sheet.setDefaultRowHeightInPoints(20);
        sheet.setDefaultRowHeight((short) (28 * 20));

        return workbook;
    }



}
